import datetime
import random

from 操作excel文档.ExcelUtil import ExcelUtil
from 操作数据库.DButils import DButils

utils = DButils(host='localhost', user='root', password='20191024', database='mysqlstudy')


def build_table():
    sql = '''
create table if not exists `user_info`(
`id` int,
`idcard` varchar(50),
`username` varchar(50),
`realname` varchar(50),
`pwd` varchar(50),
`telephone` varchar(12),
`email` varchar(12),
`age` int(11),
`sex` varchar(20),
`address` varchar(200),
`hiredate` date,
`sal` double(9,2),
`job` varchar(100),
`company` varchar(100)
)
'''
    utils.Update(sql)


def wite_data():
    global id
    ExcelUtil.write_head('user_info.xlsx', 'user_info',
                         ['id', 'idcard', 'username', 'realname', 'pwd', 'telephone', 'email', 'age', 'sex', 'address',
                          'hiredate', 'sal', 'job', 'company'])
    data = []
    for i in range(1, 2001):
        id = i
        idcard = i
        username = 'user_' + str(i)
        realname = 'realname_' + str(i)
        pwd = 'pwd_' + str(i)
        telephone = random.randint(10000000000, 99999999999)
        email = 'e' + str(i) + '@qq.com'
        age = random.randint(18, 60)
        sex = random.choice(['男', '女'])
        address = 'address_' + str(i)
        hiredate = datetime.date.today()
        sal = random.randint(1000, 10000)
        job = 'job_' + str(i)
        company = 'company_' + str(i)
        one_data = [id, idcard, username, realname, pwd, telephone, email, age, sex, address, hiredate, sal, job,
                    company]
        data.append(one_data)
    ExcelUtil.write_data('user_info.xlsx', 'user_info', data)


build_table()
wite_data()
data = ExcelUtil.get_data('user_info.xlsx', 'user_info')
for i in data:
    sql = '''
    insert into user_info values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
    '''
    one_data = tuple(i)
    print(one_data)
    utils.Update(sql, one_data)
